﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.Linq.Mapping;
using System.Data.OleDb;
using MySql.Data.MySqlClient;

namespace test4
{
    public partial class Form1 : Form
    {
        public static string mysql = "server = localhost;User id = root;password = root;Database = login";
        public static MySqlConnection mysqlConnection = new MySqlConnection(mysql);
        public Form1()
        {
            InitializeComponent();
        }
        private DataTable GetExcelData(OpenFileDialog fd) 
        {
            string fileName = fd.FileName;
            string excel = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + ";" +
            "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
            OleDbDataAdapter da = new OleDbDataAdapter("SELECT *  FROM [Sheet1$]", excel);
            DataTable dt = new DataTable();
            da.Fill(dt);  
            return dt;
        }
        private void ExecuteSql(String sql, MySqlConnection mysqlConnection)
        {
            try
            {
                mysqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(sql, mysqlConnection);
                cmd.ExecuteNonQuery();
                mysqlConnection.Close();
            }
            catch (Exception)
            {
                MessageBox.Show("error");
            } 
        }
        private DataTable GetSqlData(String sql, MySqlConnection mysqlConnection)
        {
            try
            {
                mysqlConnection.Open();
                MySqlDataAdapter da = new MySqlDataAdapter(sql, mysqlConnection);
                mysqlConnection.Close();
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
            catch (Exception)
            {
                MessageBox.Show("error");
                return null;
            }
        }
        private void button1_Click(object sender, EventArgs e)
        {
            System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();
            if (fd.ShowDialog() == DialogResult.OK)
            {
                DataTable dt = GetExcelData(fd);
                DataSet ds = new DataSet();
                ds.Tables.Add(dt);
                string sql = "";
                if (this.radioButton1.Checked)
                {
                    for (var i = 0; i < dt.Rows.Count; i++)
                    {
                        string student_sno = ds.Tables[0].Rows[i]["学号"].ToString();
                        string student_name = ds.Tables[0].Rows[i]["姓名"].ToString();
                        string student_course = ds.Tables[0].Rows[i]["班级"].ToString();
                        string student_sex = ds.Tables[0].Rows[i]["性别"].ToString();
                        this.textBox1.AppendText(student_sno + " / " + student_name + " / "
                            + student_course + " / " + student_sex + Environment.NewLine);
                        sql = "insert into student values('" + student_sno + "','" + student_name 
                            + "','" + student_course + "','" + student_sex + "')";
                        ExecuteSql(sql, mysqlConnection);
                    }
                }
                else if (this.radioButton2.Checked)
                {
                    for (var i = 0; i < dt.Rows.Count; i++)
                    {
                        string teacher_sno = ds.Tables[0].Rows[i]["教师工号"].ToString();
                        string teacher_name = ds.Tables[0].Rows[i]["姓名"].ToString();
                        string teacher_course = ds.Tables[0].Rows[i]["所带课程"].ToString();
                        string teacher_sex = ds.Tables[0].Rows[i]["性别"].ToString();
                        string teacher_class = ds.Tables[0].Rows[i]["班级"].ToString();
                        this.textBox1.AppendText(teacher_sno + " / " + teacher_name + " / "
                            + teacher_course + " / " + teacher_sex + " / " + teacher_class + Environment.NewLine);
                        sql = "insert into teacher values('" + teacher_sno + "','" + teacher_name 
                            + "','" + teacher_sex + "','" + teacher_course + "','" + teacher_class + "')";
                        ExecuteSql(sql, mysqlConnection);
                    }
                }
                else if (this.radioButton3.Checked)
                {
                    for (var i = 0; i < dt.Rows.Count; i++)
                    {
                        string course_sno = ds.Tables[0].Rows[i]["课程号"].ToString();
                        string course_name = ds.Tables[0].Rows[i]["课程名"].ToString();
                        this.textBox1.AppendText(course_sno + " / " + course_name + Environment.NewLine);
                        sql = "insert into course values('" + course_sno + "','" + course_name + "')";
                        ExecuteSql(sql, mysqlConnection);
                    }
                }
                else if (this.radioButton4.Checked)
                {
                    for (var i = 0; i < dt.Rows.Count; i++)
                    {
                        string teacher_sno = ds.Tables[0].Rows[i]["学号"].ToString();
                        string teacher_class = ds.Tables[0].Rows[i]["班级"].ToString();
                        string chinse = ds.Tables[0].Rows[i]["语文"].ToString();
                        string english = ds.Tables[0].Rows[i]["英语"].ToString();
                        string math = ds.Tables[0].Rows[i]["数学"].ToString();
                        string history = ds.Tables[0].Rows[i]["历史"].ToString();
                        string biology = ds.Tables[0].Rows[i]["生物"].ToString();
                        this.textBox1.AppendText(teacher_sno + " / " + teacher_class + " / " + chinse + " / " 
                            + english + " / " + math + " / " + history + " / " + biology + Environment.NewLine);
                        sql = "insert into grade values('" + teacher_sno + "','" + teacher_class + "','" 
                            + chinse + "','" + english + "','" + math + "','" + history + "','" + biology + "')";
                        ExecuteSql(sql, mysqlConnection);
                    }
                }
                else
                {
                    MessageBox.Show("导入选项不能为空！！！");
                }
            }
        }
        private void button2_Click(object sender, EventArgs e)
        {
            string teacher_id = this.comboBox1.Text;
            string mode = this.comboBox3.Text;
            if ((teacher_id =="")|| (mode == ""))
            {
                MessageBox.Show("查询选项不能为空！！！");
            }
            else
            {
                string sql;
                if (mode == "成绩")
                {
                    sql = "SELECT DISTINCT  course_name,teacher_class FROM course,"
                        +"teacher where course_name = teacher_course && teacher_id =" + teacher_id;
                    DataTable dt0 = GetSqlData(sql, mysqlConnection);
                    DataSet ds0 = new DataSet();
                    ds0.Tables.Add(dt0);
                    string course_name = ds0.Tables[0].Rows[0]["course_name"].ToString();
                    string class_name = ds0.Tables[0].Rows[0]["teacher_class"].ToString();
                    sql = "SELECT DISTINCT  student_id ,student_name ,student_class," + course_name 
                        + " FROM grade,student where student_id = student_id_grade AND student_class = " 
                        + class_name +" ORDER  BY student_class ,student_id";

                    DataTable dt = GetSqlData(sql, mysqlConnection);
                    DataSet ds = new DataSet();
                    ds.Tables.Add(dt);
                    this.textBox1.Text = "学号 / 姓名 / 班级 / " + course_name + Environment.NewLine;
                    for (var i = 0; i < dt.Rows.Count; i++)
                    {
                        string sno = ds.Tables[0].Rows[i]["student_id"].ToString();
                        string name = ds.Tables[0].Rows[i]["student_name"].ToString();
                        string student_class = ds.Tables[0].Rows[i]["student_class"].ToString();
                        string grade = ds.Tables[0].Rows[i][course_name].ToString();
                        this.textBox1.AppendText(sno + " / " + name + " / "
                            + student_class + " / " + grade + Environment.NewLine);
                    }
                }
                else if (mode == "平均成绩")
                {
                    sql = "SELECT DISTINCT  course_name,teacher_class FROM course,teacher "
                        + "where course_name = teacher_course && teacher_id ="+teacher_id;
                    DataTable dt0 = GetSqlData(sql, mysqlConnection);
                    DataSet ds0 = new DataSet();
                    ds0.Tables.Add(dt0);
                    string course_name = ds0.Tables[0].Rows[0]["course_name"].ToString();
                    string class_name = ds0.Tables[0].Rows[0]["teacher_class"].ToString();
                    sql = "SELECT DISTINCT  student_class,AVG(" + course_name + ")AS average"+" FROM grade,student "
                        + "where student_id = student_id_grade AND student_class = " + class_name;
                    DataTable dt = GetSqlData(sql, mysqlConnection);
                    DataSet ds = new DataSet();
                    ds.Tables.Add(dt);
                    this.textBox1.Text = " 班级 / 平均值(" + course_name + ")" + Environment.NewLine;
                    for (var i = 0; i < dt.Rows.Count; i++)
                    {
                        string student_class = ds.Tables[0].Rows[i]["student_class"].ToString();
                        string average = ds.Tables[0].Rows[i]["average"].ToString();
                        this.textBox1.AppendText(student_class + " / " + average + Environment.NewLine);
                    }
                }
                else 
                {
                    sql = "SELECT DISTINCT  teacher_course FROM teacher where teacher_id = " + teacher_id;
                    DataTable dt0 = GetSqlData(sql, mysqlConnection);
                    DataSet ds0 = new DataSet();
                    ds0.Tables.Add(dt0);
                    string course_name = ds0.Tables[0].Rows[0]["teacher_course"].ToString();
                    sql = "SELECT DISTINCT  student_class,AVG("+course_name+") AS average FROM grade,student "
                        + "WHERE student_id = student_id_grade GROUP BY student_class ORDER  BY AVG("+course_name+") DESC";
                    DataTable dt = GetSqlData(sql, mysqlConnection);
                    DataSet ds = new DataSet();
                    ds.Tables.Add(dt);
                    this.textBox1.Text = " 班级 / 班级平均成绩" + course_name + Environment.NewLine;
                    for (var i = 0; i < dt.Rows.Count; i++)
                    {
                        string student_class = ds.Tables[0].Rows[i]["student_class"].ToString();
                        string average = ds.Tables[0].Rows[i]["average"].ToString();
                        this.textBox1.AppendText(student_class + " / " + average + Environment.NewLine);
                    }
                }
            }
        }
        private void button3_Click(object sender, EventArgs e)
        {   
            string student_id = this.comboBox4.Text;
            string mode = this.comboBox6.Text;
            if ((student_id == "") || (mode == ""))
            {
                MessageBox.Show("查询选项不能为空！！");
            }
            else
            {
                string sql;
                if (mode == "成绩")
                {
                    sql = "select * from grade where student_id_grade = '" + student_id + "'";
                    DataTable dt = GetSqlData(sql, mysqlConnection);
                    DataSet ds = new DataSet();
                    ds.Tables.Add(dt);
                    if (dt.Rows.Count == 0)
                    {
                        this.textBox1.Text = "没有学号为" + student_id + "的学生。";
                    }
                    string chinese = ds.Tables[0].Rows[0]["语文"].ToString();
                    string english = ds.Tables[0].Rows[0]["英语"].ToString();
                    string math = ds.Tables[0].Rows[0]["数学"].ToString();
                    string history = ds.Tables[0].Rows[0]["历史"].ToString();
                    string biology = ds.Tables[0].Rows[0]["生物"].ToString();
                    this.textBox1.Text = "语文 / 英语 / 数学 / 历史 / 生物" + Environment.NewLine + chinese + " / "
                        + english + " / " + math + " / " + history + " / " + biology;
                }
                else
                {
                    sql = "SELECT DISTINCT *,student_name FROM grade,student "
                        + "WHERE student_id_grade = " + student_id + " AND student_id=" + student_id;
                    DataTable dt = GetSqlData(sql, mysqlConnection);
                    DataSet ds = new DataSet();
                    ds.Tables.Add(dt);
                    if (dt.Rows.Count == 0)
                    {
                        this.textBox1.Text = "没有学号为" + student_id + "的学生。";
                    }
                    else
                    {
                        string sno = ds.Tables[0].Rows[0]["student_id"].ToString();
                        string name = ds.Tables[0].Rows[0]["student_name"].ToString();
                        string student_class = ds.Tables[0].Rows[0]["student_class"].ToString();
                        string chinese = ds.Tables[0].Rows[0]["语文"].ToString();
                        string english = ds.Tables[0].Rows[0]["英语"].ToString();
                        string math = ds.Tables[0].Rows[0]["数学"].ToString();
                        string history = ds.Tables[0].Rows[0]["历史"].ToString();
                        string biology = ds.Tables[0].Rows[0]["生物"].ToString();
                        int average = (int.Parse(chinese) + int.Parse(english) + int.Parse(math) 
                            + int.Parse(history) + int.Parse(biology)) / 5;
                        this.textBox1.Text = "学号 / 姓名 / 班级 / 平均值" + Environment.NewLine + sno + " / "
                            + name + " / " + student_class + " / " + average.ToString();
                    }
                }
            }
        }
        private void button4_Click(object sender, EventArgs e)
        {
            string student_id = this.comboBox4.Text;
            if (student_id == "")
            {
                MessageBox.Show("查询选项不能为空！！！");
            }
            else
            {
                string sql = "select * from grade where student_id_grade = '" + student_id + "'";
                DataTable dt = GetSqlData(sql, mysqlConnection);
                DataSet ds = new DataSet();
                ds.Tables.Add(dt);
                if (dt.Rows.Count == 0)
                {
                    this.textBox1.Text = "没有学号为" + student_id + "的学生。";
                }
                string chinese = ds.Tables[0].Rows[0]["语文"].ToString();
                string english = ds.Tables[0].Rows[0]["英语"].ToString();
                string math = ds.Tables[0].Rows[0]["数学"].ToString();
                string history = ds.Tables[0].Rows[0]["历史"].ToString();
                string biology = ds.Tables[0].Rows[0]["生物"].ToString();
                this.textBox1.Text = "语文 / 英语 / 数学 / 历史 / 生物" + Environment.NewLine + chinese + " / "
                    + english + " / " + math + " / " + history + " / " + biology;   
            }
        }
        private void button5_Click(object sender, EventArgs e)
        {
            string sql = "SELECT DISTINCT  * FROM grade,student WHERE student_id=student_id_grade";
            DataTable dt = GetSqlData(sql, mysqlConnection);
            DataSet ds = new DataSet();
            ds.Tables.Add(dt);
            if (dt.Rows.Count == 0)
            {
                MessageBox.Show("没有考试成绩！！！");
            }
            this.textBox1.Text = "学号 / 姓名 / 班级 / 语文 / 英语 / 数学 / 历史 / 生物" + Environment.NewLine;
            for (var i = 0; i < dt.Rows.Count; i++)
            {
                string sno = ds.Tables[0].Rows[i]["student_id"].ToString();
                string name = ds.Tables[0].Rows[i]["student_name"].ToString();
                string student_class = ds.Tables[0].Rows[i]["student_class"].ToString();
                string chinese = ds.Tables[0].Rows[i]["语文"].ToString();
                string english = ds.Tables[0].Rows[i]["英语"].ToString();
                string math = ds.Tables[0].Rows[i]["数学"].ToString();
                string history = ds.Tables[0].Rows[i]["历史"].ToString();
                string biology = ds.Tables[0].Rows[i]["生物"].ToString();
                this.textBox1.AppendText(sno + " / " + name + " / "
                    + student_class + " / " + chinese + " / " + english + " / "
                    + math + " / " + history + " / " + biology + Environment.NewLine);
            } 
        }
    }
}
